import time

from Case.python_ui.Common.do_mysql import DoMysql
from Case.python_ui.Test_data.common_data import Commondata
from Case.python_ui.Test_data.login_data import Logindata
from Case.python_ui.Common.commontool import CommonTool
from Case.python_ui.Test_data.menu_name_data import MenuName


class BusinessCommonTools(object):

    def  get_verification_code(self,cmdopt,email,database_object):
        time.sleep(5)
        database=Commondata.db_config[cmdopt]['database'][0]
        query_verification_code=Commondata.query_user_validation_code_sql
        query_sql=query_verification_code.replace('@a',email)
        data_object,pool=CommonTool(database_object).get_data_source(database,cmdopt)
        query_result=data_object.do_mysql(query_sql,pool)
        print(query_result)
        code=query_result[0]['validation_code']
        return code


    def get_instance_id(self,cmdopt, database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        query_result = Commondata.query_instance_id
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        query_result = data_object.do_mysql(query_result, pool)
        print(query_result)
        id = query_result[0]['resource_id']
        return id



    def get_organization(self,cmdopt,org_name,database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        query_org_sql = Commondata.query_org.replace('@a',org_name)
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        query_result=data_object.do_mysql(query_org_sql,pool)
        print(query_result)
        return query_result


    def get_user_state(self,cmdopt,email,database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        query_user_state = Commondata.query_user_state.replace('@a', email)
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        query_user_state =data_object.do_mysql(query_user_state,pool)
        print(query_user_state)
        return query_user_state


    def get_org_name(self,cmdopt,email,database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        query_org_name=Commondata.quer_org_name.replace('@a',email)
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        org_name=data_object.do_mysql(query_org_name,pool)[0]['org_name']
        return org_name

    def get_user_group_name(self,cmdopt,email,database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        query_ug_name = Commondata.query_user_group_name.replace('@a', email)
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        ug_name=data_object.do_mysql(query_ug_name,pool)[0]['user_group_name']
        return ug_name


    def get_user_role(self,cmdopt,email,database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        query_ur_name = Commondata.query_user_role.replace('@a', email)
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        ur_name=data_object.do_mysql(query_ur_name,pool)[0]['role_name']
        return ur_name

    def get_admin_user_role(self,cmdopt,email,database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        query_ur_name = Commondata.query_admin_user_role.replace('@a', email)
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        ur_name=data_object.do_mysql(query_ur_name,pool)[0]['role_name']
        return ur_name


    def get_user_group_state(self,cmdopt,user_group,database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        query_ug_state=Commondata.query_user_group_state.replace('@a',user_group)
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        ug_state=data_object.do_mysql(query_ug_state,pool)[0]['user_group_state']
        return ug_state


    def get_user_user_group(self,cmdopt,usergroup,database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        query_uugm=Commondata.query_user_user_group_member.replace('@a',usergroup)
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        uugm=data_object.do_mysql(query_uugm,pool)
        return uugm

    def get_user_user_group_name(self, cmdopt, email, database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        query_uugmn = Commondata.query_user_user_group_name.replace('@a', email)
        print(query_uugmn)
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        uugmn=data_object.do_mysql(query_uugmn,pool)[0]['user_group_name']
        print(uugmn)
        return uugmn

    def get_admin_user_group_name(self, cmdopt, email, database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        query_uugmn = Commondata.query_admin_user_group_name.replace('@a', email)
        print(query_uugmn)
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        uugmn=data_object.do_mysql(query_uugmn,pool)[0]['user_group_name']
        print(uugmn)
        return uugmn



    def get_user_invite_log_status(self,cmdopt, email, database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        query_user_status=Commondata.query_uninvited_user_status.replace('@a',email)
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        status=data_object.do_mysql(query_user_status,pool)[0]['status']
        return status


    def get_order_id(self,cmdopt,order_id,database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        query_cust_id=Commondata.query_custid_by_order_id.replace('@a',order_id)
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        partner_cust_id=data_object.do_mysql(query_cust_id,pool)[0]['partner_cust_id']
        print(partner_cust_id)
        return partner_cust_id




    def upadte_credit_limit(self,cmdopt,value,database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        update_sql=Commondata.update_creadit_limit.replace('@a',Logindata.user_data[cmdopt]['user']['username']).replace('@b',value)
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        data_object.do_mysql(update_sql, pool)

    def upadte_prepay_credit_limit(self,cmdopt,value,database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        update_sql=Commondata.update_creadit_limit.replace('@a',Logindata.user_data[cmdopt]['user2']['username']).replace('@b',value)
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        data_object.do_mysql(update_sql, pool)


    def update_job_slb_release_next_trigger_time(self,cmdopt,database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        update_result = Commondata.update_next_trigger_time
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        data_object.do_mysql(update_result, pool)


    def update_job_slb_renew_next_trigger_time(self,cmdopt,database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        update_result = Commondata.update_next_trigger_time_renew
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        data_object.do_mysql(update_result, pool)


    def update_slb_expired_time(self,cmdopt,database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        update_result = Commondata.update_slb_expired_time.replace()
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        data_object.do_mysql(update_result, pool)


    def get_gross_amount(self,cmdopt,database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        query_gross_mount_sql=Commondata.query_gross_amount.replace('@a',Logindata.user_data[cmdopt]['user']['username'])
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        gross_amount=data_object.do_mysql(query_gross_mount_sql, pool)[0]['gross_amount']
        gross_amount_result=str(round(gross_amount,2))
        print(gross_amount_result)
        return gross_amount_result

    def get_pretax_amount(self, cmdopt, database_object,billing_cycle):
        database = Commondata.db_config[cmdopt]['database'][0]
        query_pretax_gross_amount=Commondata.query_pretax_gross_amount.replace('@a',Logindata.user_data[cmdopt]['user2']['username']).replace('@b',billing_cycle)
        print(query_pretax_gross_amount)
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        pretax_gross_amount=data_object.do_mysql(query_pretax_gross_amount, pool)[0]['pretax_gross_amount']
        pretax_gross_amount_result=str(round(pretax_gross_amount,2))
        print(pretax_gross_amount_result)
        return pretax_gross_amount_result

    def get_pretax_amount_bussiness(self, cmdopt, database_object,billing_cycle):
        database = Commondata.db_config[cmdopt]['database'][0]
        query_pretax_gross_amount=Commondata.query_pretax_gross_amount.replace('@a',Logindata.user_data[cmdopt]['bussiness_user']['username']).replace('@b',billing_cycle)
        print(query_pretax_gross_amount)
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        pretax_gross_amount=data_object.do_mysql(query_pretax_gross_amount, pool)[0]['pretax_gross_amount']
        pretax_gross_amount_result=str(round(pretax_gross_amount,2))
        print(pretax_gross_amount_result)
        return pretax_gross_amount_result

    # 根据所在地区，查看network可创建数量
    def get_network_num(self,cmdopt, database_object,vcenter):
        time.sleep(5)
        database = Commondata.db_config[cmdopt]['database'][0]
        search_network_create_num = MenuName.search_network_create_num
        search_network_create_num_sql = search_network_create_num.replace('@a',vcenter)
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        query_result = data_object.do_mysql(search_network_create_num_sql, pool)
        num = query_result[0]['limit_count']
        print(query_result)
        return int(num)

    # 根据币种查询total值
    def get_num_total_by_settlement_currency(self,cmdopt, database_object,currency):
        time.sleep(5)
        database = Commondata.db_config[cmdopt]['database'][0]
        search_total_by_settlement_currency = MenuName.search_total_by_settlement_currency
        search_total_by_settlement_currency_sql = search_total_by_settlement_currency.replace('@a',currency)
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        query_result = data_object.do_mysql(search_total_by_settlement_currency_sql, pool)
        gross_amount = query_result[0]['sum(gross_amount)']
        paid_amount = query_result[0]['sum(paid_amount)']
        gross_amount_paid_amount = query_result[0]['sum(gross_amount-paid_amount)']
        print('gross_amount',gross_amount)
        print('paid_amount', paid_amount)
        print('gross_amount_paid_amount', gross_amount_paid_amount)
        #return int(num)
        if gross_amount is None:
            gross_amount_result=0
        else:
            gross_amount_result=round(gross_amount,2)
        if paid_amount is None:
            paid_amount_result = 0
        else:
            paid_amount_result = round(paid_amount, 2)
        if gross_amount_paid_amount is None:
            gross_amount_paid_amount_result = 0
        else:
            gross_amount_paid_amount_result = round(gross_amount_paid_amount, 2)
        return gross_amount_result,paid_amount_result,gross_amount_paid_amount_result


    # 执行Ascm配置每日校验 ，生成new events事件
    def update_job_info(self,cmdopt,database_object):
        database = Commondata.db_config[cmdopt]['database'][0]
        update_result = MenuName.update_job_info
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)
        data_object.do_mysql(update_result, pool)